drop proc spa_SEC_GetUserPermissions
go
/****** Object:  StoredProcedure [dbo].[spa_SEC_GetUserPermissions]    Script Date: 04/29/2011 16:32:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*********************************************************************************************
* PROCEDURE:	[spa_SEC_GetUserPermissions]
* PURPOSE: Get permissions of current user
* NOTES:
* CREATED:	Nha Doan	04/14/2011
* MODIFIED:	
*/
/***************************************************************************************
 DATE			VERSION	TICKET	AUTHOR			DESCRIPTION
*----------------------------------------------------------------------------------------
* 04/14/2011	1.00	16131	Nha Doan		Creation.
****************************************************************************************/
/*
Ex:
spa_SEC_GetUserPermissions '{21CAFCEA-8629-4FA9-9DC0-85A98AD344D2}'
*/
CREATE PROCEDURE [dbo].[spa_SEC_GetUserPermissions]
	@UserRecId uniqueidentifier
AS

SELECT DISTINCT P.*
FROM SEC_PermissionsInRole PH (nolock)
	JOIN SEC_Permissions P (nolock) ON PH.PermissionId = P.PermissionId OR PH.PermissionId IS NULL
	JOIN SEC_UsersInRole UH (nolock) ON PH.RoleId = UH.RoleId
	LEFT JOIN (SELECT PermissionId FROM SEC_UserPermissionExcluded (nolock) WHERE UserRecID = @UserRecId) PE ON PE.PermissionId = P.PermissionId
WHERE UH.UserRecID = @UserRecId
	AND PE.PermissionId IS NULL
GO